﻿using CNative.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace CNative.DbUtils
{
    public class DbMaintenanceProvider : SqlBuilder
    {
        protected string DbName { get; set; }

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbName">构造参数，可以为数据库连接字符串</param>
        public DbMaintenanceProvider(string dbName) : base(dbName)
        {
            DbName = dbName;
        }
        #endregion

        #region DML CodeFirst
        /// <summary>
        /// 得到所有数据库
        /// </summary>
        public virtual List<string> GetAllDatabases()
        {
            var sql = this.CreateSqlEntity();
            var colname = "name";
            if (DBType == DatabaseType.MySql)
            {
                colname = "database";
            }
            else if (DBType == DatabaseType.Sqlite || DBType == DatabaseType.MsAccess)
            {
                return new List<string>() { "Tables" };
            }
            sql.Sql = SqlDbProvider.GetDataBaseSql;
            return GetObjects(sql, colname);
        }
        /// <summary>
        /// 得到数据库中的所有表
        /// </summary>
        public virtual List<string> GetAllTables(string dbName)
        {
            var sql = this.CreateSqlEntity();
            var colname = "name";
            #region Oracle/Sqlite/MsAccess
            if (DBType == DatabaseType.Oracle)
            {
                if (dbName.IsNullOrEmpty())
                    dbName = "SYS";
                dbName = dbName.ToUpper();
            }
            else if (DBType == DatabaseType.Sqlite)
            {
                dbName = "";
            }
            else if (DBType == DatabaseType.MsAccess)
            {
                return Funs.GetTableNameList(this, "TABLE");
            }
            #endregion
            sql.Sql = string.Format(SqlDbProvider.GetTableInfoListSql, dbName);
            return GetObjects(sql, colname, "Description");
        }
        /// <summary>
        /// 得到数据库中的所有视图
        /// </summary>
        public virtual List<string> GetAllViews(string dbName)
        {
            var sql = CreateSqlEntity();
            #region Oracle/Sqlite/MsAccess
            if (DBType == DatabaseType.Oracle)
            {
                if (dbName.IsNullOrEmpty())
                    dbName = "SYS";
                dbName = dbName.ToUpper();
            }
            else if (DBType == DatabaseType.Sqlite)
            {
                dbName = "";
            }
            else if (DBType == DatabaseType.MsAccess)
            {
                return Funs.GetTableNameList(this,"VIEW");
            }
            #endregion
            sql.Sql = string.Format(SqlDbProvider.GetViewInfoListSql, dbName);
            return GetObjects(sql, "name", "Description");
        }

        /// <summary>
        /// 得到一个表的所有列信息
        /// </summary>
        public virtual List<DbColumnInfo> GetAllColumns(string tableName, string dbName)
        {
            if (tableName.IsNullOrEmpty())
                return new List<DbColumnInfo>();

            if (DBType == DatabaseType.Sqlite)
            {
                dbName = "";
            }
            return Funs.CacheGet(DbName + tableName, () =>
             {
                 return Funs.GetDbDbColumnInfo(this, tableName, dbName);
                 //if (DBType == DatabaseType.Sqlite)
                 //{
                 //    var sqle = CreateSqlEntity();
                 //    sqle.Sql = string.Format(SqlDbProvider.GetColumnInfosByTableNameSql, "", tableName);
                 //    var dt = this.QueryDataTable(sqle);
                 //    if (dt != null && dt.Rows.Count > 0)
                 //    {
                 //        dt.Columns.Add(new DataColumn("table_name"));
                 //        dt.Columns.Add(new DataColumn("column_type"));
                 //        dt.Columns.Add(new DataColumn("MaxLength"));
                 //        foreach (DataColumn column in dt.Columns)
                 //        {
                 //            if (column.ColumnName.ToLower() == "cid") column.ColumnName = "ColumnId";
                 //            else if (column.ColumnName.ToLower() == "notnull") column.ColumnName = "IsNullable";
                 //            else if (column.ColumnName.ToLower() == "pk") column.ColumnName = "IsPrimaryKey";
                 //        }
                 //        foreach (DataRow dr in dt.Rows)
                 //        {
                 //            try
                 //            {
                 //                var typestr = dr["type"].NullToStr();
                 //                var typestrs = typestr.Split('(');
                 //                dr["table_name"] = "tableName";
                 //                dr["column_type"] = typestr;
                 //                dr["type"] = typestrs[0];
                 //                try
                 //                {
                 //                    dr["MaxLength"] = typestrs.Length > 1 ? typestrs[1].Split(',', '(', ')')[0] : "0";
                 //                }
                 //                catch { }
                 //            }
                 //            catch { }
                 //        }
                 //        var list = EntityHelper.DataTableToList<DbColumnInfo>(dt);
                 //        return list;
                 //    }
                 //    return new List<DbColumnInfo>();
                 //}
                 //else if (DBType == DatabaseType.MsAccess)
                 //{
                 //    return Funs.GetTableFieldNameList(this.ConnectString, $"{tableName}");
                 //}
                 //else
                 //{
                 //    var sqle = CreateSqlEntity();
                 //    sqle.Sql = string.Format(SqlDbProvider.GetColumnInfosByTableNameSql, dbName, tableName);
                 //    var list = Query<DbColumnInfo>(sqle);

                 //    return list;
                 //}
             });
        }

        /// <summary>
        /// 得到数据库对象
        /// </summary>
        protected List<string> GetObjects(SqlEntity sql, string colName = "name", string colName2 = "")
        {
            DataSet ds = this.QueryDataSet(sql);

            var objs = new List<string>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (colName2.IsNullOrEmpty())
                        objs.Add(ds.Tables[0].Rows[i][colName].ToString());
                    else
                        objs.Add(ds.Tables[0].Rows[i][colName].ToString() + (ds.Tables[0].Rows[i][colName2] != null && ds.Tables[0].Rows[i][colName2] != DBNull.Value ? "(" + ds.Tables[0].Rows[i][colName2].ToString() + ")" : ""));
                }
            }
            return objs;
        }
        #endregion
    }
}
